USE [influenza]
GO

/****** Object:  StoredProcedure [dbo].[SP_SemanaEpi]    Script Date: 04/08/2010 18:23:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[SP_SemanaEpi]
@param varchar(100)

as
Begin

DECLARE @FechIni datetime
DECLARE @FechFin datetime

Select @FechIni=fechaIni, @FechFin=FechaFin from Cat_SemEpi where Semana=@param and Anio=(YEAR(GETDATE()))

Select convert(varchar(10),@FechIni,103) as 'FechaIni',convert(varchar(10),@FechFin,103) as 'FechaFin',
[INF AH1N1 PMD] as 'AH1N1',[NEGATIVO] as 'NEG',[INF A] as 'A',[INF B] as 'B',[NO ADECUADO] as 'NOADECUADO',[ADENOVIRUS] as 'ADE',[A H1] as 'AH1',[A H3] as 'AH3',
[NO SUBTIPIFICADO] as 'ANS',([PARAINFLUENZA 1]+[PARAINFLUENZA 2]+[PARAINFLUENZA 3]+[PARAINFLUENZA 4]) as 'PINF',
([VSR]+[VSR A]+[VSR B]) as 'VSR',
([INF AH1N1 PMD]+[INF A]+[INF B]+[A H1]+[A H3]+[ADENOVIRUS]+[NO SUBTIPIFICADO]+
[PARAINFLUENZA 1]+[PARAINFLUENZA 2]+[PARAINFLUENZA 3]+[PARAINFLUENZA 4]+[VSR]+[VSR A]+[VSR B]+[NEGATIVO]) as 'tCasosProbable', 
([INF AH1N1 PMD]+[INF A]+[INF B]+[A H1]+[A H3]+[ADENOVIRUS]+[NO SUBTIPIFICADO]+
[PARAINFLUENZA 1]+[PARAINFLUENZA 2]+[PARAINFLUENZA 3]+[PARAINFLUENZA 4]+[VSR]+[VSR A]+[VSR B]) as 'tCasosConfirmados'
from (
Select b.resultados
from dat_laboratorio a 
inner join cat_lab_resultados b on(a.nResultado=b.id_resultados)
inner join aux_laboratorio_res c on (a.id_principal=c.id_dat_laboratorio)
Where a.fResultado between @FechIni and @FechFin
and  a.nEstudio=1
) pvt
pivot(count(resultados) for resultados in([INF AH1N1 PMD],[NEGATIVO],[INF A],[INF B],[NO ADECUADO],[ADENOVIRUS],
[PARAINFLUENZA 1],[PARAINFLUENZA 2],[PARAINFLUENZA 3],[PARAINFLUENZA 4],[NO SUBTIPIFICADO],[A H1],[A H3],[VSR],[VSR A],[VSR B]))as child
End
GO

